﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using System.IO;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;

namespace MISAPP.Common
{
    public class DAL
    {
        private static Database db = null;
        protected static string defaultDB = "MSSQL";
        protected static string configFile = "Common.dll.config";

        public static DbCommand GetCommand()
        {
            //return GetCommand(defaultDB);
            return GetCommand(null);
        }

        protected static DatabaseProviderFactory getDatabaseProvider()
        {            
            string configPath = string.Empty;
            if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + configFile)) //如果這一層沒有，就再加bin
            {
                configPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, configFile);
            }
            else
            {
                configPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory + @"bin\", configFile);
            }
            return new DatabaseProviderFactory(new FileConfigurationSource(configPath));
        }

        public static DbCommand GetCommand(string p_db)
        {
            //db = EnterpriseLibraryContainer.Current.GetInstance<Database>(p_db);
            DatabaseProviderFactory fact = getDatabaseProvider();
            if (p_db == null)
            {
                db = fact.CreateDefault();
            }
            else
            {
                db = fact.Create(p_db);
            }
            DbCommand cmd =db.GetSqlStringCommand("SELECT");
            cmd.CommandTimeout = 3600;
            return cmd;
        }

        public static DataTable GetDataTable(IDbCommand p_cmd)
        {
            DataTable dt = new DataTable();
            try
            {
                //DbCommand cmd = p_cmd as DbCommand;
                //DataAdapter da = db.GetDataAdapter();
                //DataSet ds = new DataSet();
                //db.LoadDataSet(cmd, ds, "a");
                //dt = ds.Tables[0];
                dt.Load(GetDataReader(p_cmd));
            }
            catch (System.Data.Common.DbException ex)
            {
                exceptionHandle(ex);
            }
            catch (Exception ex)
            {
                exceptionHandle(ex);
            }
            finally
            {

            }
            return dt;
        }

        public static IDataReader GetDataReader(IDbCommand p_cmd)
        {
            DbCommand cmd = p_cmd as DbCommand;
            IDataReader reader = null;
            try
            {
                reader = db.ExecuteReader(cmd);
            }
            catch (System.Data.Common.DbException ex)
            {
                exceptionHandle(ex);
            }
            catch (Exception ex)
            {
                exceptionHandle(ex);
            }
            finally
            {
                // Connection.Close();
            }
            return reader;
        }

        /// <summary>
        /// 回傳 IDbDataParameter
        /// </summary>
        /// <param name="p_name">欄位名</param>
        /// <param name="p_dbType">欄位型態</param>
        /// <param name="p_value">值</param>
        /// <returns>IDbDataParameter</returns>
        public static DbParameter GetParameter(string p_name, DbType p_dbType, object p_value, DbParameter p_para)
        {
            p_para.ParameterName = p_name;
            p_para.DbType = p_dbType;
            p_para.Value = p_value;
            return p_para;
        }

        /// <summary>
        /// 要傳入的 Command 要先把Parameter 組好
        /// </summary>
        /// <param name="p_cmd">IDBCommand</param>
        /// <returns>int</returns>
        public static int ExecuteNoneQuery(DbCommand p_cmd)
        {
            int iResult = -1;
            try
            {
                iResult = db.ExecuteNonQuery(p_cmd);
                
            }
            catch (DbException ex)
            {
                exceptionHandle(ex);
            }
            catch (Exception ex)
            {
                exceptionHandle(ex);
            }
            finally
            {
            }
            return iResult;
        }

        /// <summary>
        /// 利用DtatTable 看是不否會快點
        /// </summary>        
        /// <returns></returns>
        public static int ExecuteNoneQuery(DataTable p_dt,string p_sp_name,string p_paraName)
        {
            
            System.Data.SqlClient.SqlCommand scmd = db.GetStoredProcCommand(p_sp_name)
                as System.Data.SqlClient.SqlCommand;

            int iResult = -1;
            scmd.Parameters.AddWithValue(p_paraName, p_dt);
            iResult = db.ExecuteNonQuery(scmd);            
            return iResult;
        }

        protected static void exceptionHandle(Exception p_ex)
        {
            throw new Exception(p_ex.Message);
        }



    }
}
